package matlap.database.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import matlap.database.ConnectionManager;
import matlap.database.dao.TeacherDAO;
import matlap.shared.Teacher;
import matlap.shared.exceptions.MyException;

public class TeacherJdbcDAO implements TeacherDAO {

	Connection con = null;

	/**
	 * Turns back the teachers FROM the people table in a Teacher list.
	 */

	public List<Teacher> getTeachers() {

		List<Teacher> list = new ArrayList<Teacher>();
		String firstName;
		String lastName;
		String address;
		String userName;
		String query = "SELECT last_name, first_name, people.username AS username, address, accept FROM people,teacher WHERE "
				+ " role=2 AND people.username=teacher.username ORDER BY last_name, first_name";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			int i = 0;

			while (rs.next()) {

				firstName = rs.getString("first_name");
				lastName = rs.getString("last_name");
				address = rs.getString("address");
				userName = rs.getString("username");
				Teacher teach = new Teacher(firstName, lastName, userName,
						address);
				teach.setAccept(rs.getInt("accept"));
				list.add(i, teach);
				i++;

			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;

	}

	/**
	 * Turns back the teachers number FROM the people table.
	 */

	@Override
	public int getTeachersNumber() {

		int nr = 0;
		String query = "SELECT COUNT(username) AS darab FROM people WHERE role=2";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			while (rs.next()) {
				nr = Integer.parseInt(rs.getString("darab"));
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return nr;
	}

	/**
	 * Turns back the teachers name FROM the people table in a Teacher list.
	 */

	@Override
	public List<Teacher> selectTeachersName() {

		List<Teacher> list = new ArrayList<Teacher>();
		String firstName;
		String lastName;
		String query = "SELECT last_name,first_name FROM people WHERE role=2 ORDER BY last_name, first_name";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			int i = 0;

			while (rs.next()) {

				firstName = rs.getString("first_name");
				lastName = rs.getString("last_name");
				Teacher stu = new Teacher(firstName, lastName, null, null);

				list.add(i, stu);
				i++;
			}

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;

	}

	/**
	 * Turns back the teachers FROM the people table in a Teacher list by adding
	 * teachers name.
	 */

	@Override
	public List<Teacher> getTeachersByName(String teachersName) {

		List<Teacher> list = new ArrayList<Teacher>();
		String firstName;
		String lastName;
		String address;
		String userName;
		String query = "";
		if (teachersName.length() != 0) {
			String[] n = teachersName.split(" ");
			if (n.length == 2)
				query = "SELECT last_name, first_name, people.username as username, address, accept FROM people,teacher WHERE "
						+ " role=2 AND ((last_name like '%"
						+ n[1]
						+ "%' AND first_name like '%"
						+ n[0]
						+ "%') OR (last_name like '%"
						+ n[0]
						+ "%' AND first_name like '%"
						+ n[1]
						+ "%')) AND people.username=teacher.username";
			else
				query = "SELECT last_name, first_name, people.username as username, address, accept FROM people,teacher WHERE "
						+ " role=2 AND (last_name like '%"
						+ teachersName
						+ "%' OR first_name like '%"
						+ teachersName
						+ "%') AND people.username=teacher.username";

		} else {
			query = "SELECT last_name, first_name, people.username as username, address, accept FROM people,teacher WHERE "
					+ " role=2 AND people.username=teacher.username";
		}
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			int i = 0;

			while (rs.next()) {

				firstName = rs.getString("first_name");
				lastName = rs.getString("last_name");
				address = rs.getString("address");
				userName = rs.getString("username");
				Teacher teach = new Teacher(firstName, lastName, userName,
						address);
				teach.setAccept(rs.getInt("accept"));
				list.add(i, teach);
				i++;

			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	/**
	 * Turns back the teachers username FROM the people table in a String list.
	 */

	public List<String> getUserNames() {

		List<String> list = new ArrayList<String>();
		String userName;
		String query = "SELECT username FROM people WHERE role=2 ORDER BY username";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);

			int i = 0;

			while (rs.next()) {

				userName = rs.getString("username");
				list.add(i, userName);
				i++;

			}

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * Deletes teachers FROM the people table by adding username.
	 */

	@Override
	public void deleteTeacher(String userName) throws MyException{

		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();

			String s = new String("DELETE FROM teacher WHERE username='"
					+ userName + "'");
			st.executeUpdate(s);

			s = new String("DELETE FROM registered WHERE username='" + userName
					+ "'");
			st.executeUpdate(s);

			s = new String("DELETE FROM people WHERE username='" + userName
					+ "'");
			st.executeUpdate(s);

		} catch (SQLException ex) {
			throw new MyException("Nem lehet törölni!");
		} catch (ClassNotFoundException e) {
			throw new MyException("Nem lehet törölni!");
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
			}
		}

	}

	/**
	 * Updates teachers in the people table by adding username, first name, last
	 * name AND address.
	 */

	@Override
	public void updateTeacher(String userName, String firstName,
			String lastName, String address, int accept) throws MyException {

		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			String s = "";
			if (lastName != "") {
				s = new String("UPDATE people SET last_name='" + lastName
						+ "'WHERE username='" + userName + "'");
				st.executeUpdate(s);
			}
			if (firstName != "") {
				s = new String("UPDATE people SET first_name='" + firstName
						+ "'WHERE username='" + userName + "'");
				st.executeUpdate(s);
			}
			if (address != "") {
				s = new String("UPDATE people SET address='" + address
						+ "'WHERE username='" + userName + "'");
				st.executeUpdate(s);
			}
			s = new String("UPDATE teacher SET accept='" + accept
					+ "'WHERE username='" + userName + "'");
			st.executeUpdate(s);

		} catch (SQLException ex) {
			throw new MyException("Nem sikerült a frissités. Ellenőrizd az adatokat és probálkozz újra!");
		} catch (ClassNotFoundException e) {
			throw new MyException("Nem sikerült a frissités. Ellenőrizd az adatokat és probálkozz újra!");
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	/**
	 * Turns back a teacher FROM the people table in Teacher by adding username.
	 */

	@Override
	public Teacher getTeacherByUserName(String userName) {

		String firstName;
		String lastName;
		String address;
		Teacher techer = null;
		String query = "SELECT last_name, first_name, address,accept FROM people,teacher WHERE people.username='"
				+ userName + "' AND people.username=teacher.username";

		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);

			while (rs.next()) {

				firstName = rs.getString("first_name");
				lastName = rs.getString("last_name");
				address = rs.getString("address");
				techer = new Teacher(firstName, lastName, userName, address);
				techer.setAccept(rs.getInt("accept"));
			}

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return techer;
	}

}
